﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;

/// <summary>
/// Summary description for IScheduleDAO
/// </summary>
public class MySQLScheduleDAOImpl : AbstractDAO, IScheduleDAO {
	public MySQLScheduleDAOImpl(AbstractDAOFactory factory) : base (factory) { }

	public void saveSchedule(Schedule schedule) {
		if (schedule.Id > 0) {
			this.updateSchedule(schedule);
		} else {
			this.createSchedule(schedule);
		}
	}

	protected void createSchedule(Schedule schedule) {
		StringBuilder sql = new StringBuilder();
		sql.Append("INSERT INTO Schedule (User_ID, From_Destination_ID, dayOfWeek, departureHour, returnHour)");
		sql.Append(" VALUES (?, ?, ?, ?, ?)");

		using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
			conn.Open();

			OdbcCommand cmd = conn.CreateCommand();
			cmd.CommandText = sql.ToString();
			cmd.Parameters.Add(new OdbcParameter("@User_ID", schedule.User.Id));
			cmd.Parameters.Add(new OdbcParameter("@From_Destination_ID", schedule.From.Id));
			cmd.Parameters.Add(new OdbcParameter("@dayOfWeek", schedule.DayOfWeek.ToString()));
			cmd.Parameters.Add(new OdbcParameter("@departureHour", schedule.DepartureHour));
			cmd.Parameters.Add(new OdbcParameter("@returnHour", schedule.ReturnHour));
			cmd.ExecuteNonQuery();
			cmd.Dispose();

			//Query updated ID
			OdbcCommand idCmd = conn.CreateCommand();
			idCmd.CommandText = "SELECT @@IDENTITY";
			schedule.Id = (int) idCmd.ExecuteScalar();
		}
	}

	protected void updateSchedule(Schedule schedule) {
		StringBuilder sql = new StringBuilder();
		sql.Append("UPDATE Schedule SET User_ID=?, From_Destination_ID=?, dayOfWeek=?, departureHour=?, returnHour=? WHERE ID=?");

		using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
			conn.Open();

			OdbcCommand cmd = conn.CreateCommand();
			cmd.CommandText = sql.ToString();
			cmd.Parameters.Add(new OdbcParameter("@User_ID", schedule.User.Id));
			cmd.Parameters.Add(new OdbcParameter("@From_Destination_ID", schedule.From.Id));
			cmd.Parameters.Add(new OdbcParameter("@dayOfWeek", schedule.DayOfWeek.ToString()));
			cmd.Parameters.Add(new OdbcParameter("@departureHour", schedule.DepartureHour));
			cmd.Parameters.Add(new OdbcParameter("@returnHour", schedule.ReturnHour));
			cmd.Parameters.Add(new OdbcParameter("@ID", schedule.Id));
			cmd.ExecuteNonQuery();
			cmd.Dispose();
		}
	}

	public List<Schedule> readSchedules(ScheduleSearchCriteria criteria) {
		List<Schedule> result = new List<Schedule>();

		StringBuilder sql_select = new StringBuilder();
		StringBuilder sql_from = new StringBuilder();
		StringBuilder sql_where = new StringBuilder();

		sql_select.Append("SELECT Schedule.ID AS ID, dayOfWeek, departureHour, returnHour, From_Destination_ID, Schedule.User_ID AS User_ID");
		
		sql_from.Append(" FROM Schedule, Destination");

		sql_where.Append(" WHERE Schedule.From_Destination_ID = Destination.ID");

		using (OdbcConnection conn = (OdbcConnection) this.factory.getConnection()) {
			conn.Open();

			OdbcCommand cmd = conn.CreateCommand();

			if (criteria.DayOfWeek != null) {
				sql_where.Append(" AND dayOfWeek=?");
				cmd.Parameters.Add(new OdbcParameter("@dayOfWeek", criteria.DayOfWeek.ToString()));
			}

			if (criteria.DepartureHour != null) {
				sql_where.Append(" AND departureHour=?");
				cmd.Parameters.Add(new OdbcParameter("@departureHour", criteria.DepartureHour));
			}

			if (criteria.ReturnHour != null) {
				sql_where.Append(" AND returnHour=?");
				cmd.Parameters.Add(new OdbcParameter("@returnHour", criteria.ReturnHour));
			}

			if (criteria.Zip != null) {
				sql_where.Append(" AND zip=?");
				cmd.Parameters.Add(new OdbcParameter("@zip", criteria.Zip));
			}

			if ((criteria.Users != null) && (criteria.Users.Count > 0)) {
				sql_where.Append(" AND Schedule.User_ID IN (");
				int i = 0;
				foreach (User user in criteria.Users.Values) {
					sql_where.Append(user.Id);
					if (i < (criteria.Users.Count - 1)) {
						sql_where.Append(", ");
					}
					i++;
				}
				sql_where.Append(")");
			}

			cmd.CommandText = sql_select.ToString() + sql_from.ToString() + sql_where.ToString();

			Dictionary<int, User> unknownUserIDs = new Dictionary<int, User>();

			OdbcDataReader rs = cmd.ExecuteReader();
			while (rs.Read()) {
				Schedule schedule = new Schedule();

				schedule.Id = (int) rs["ID"];
				schedule.DayOfWeek = (DayOfWeek) Enum.Parse(typeof(DayOfWeek), (string) rs["dayOfWeek"]);
				schedule.DepartureHour = (int) rs["departureHour"];
				schedule.ReturnHour = (int) rs["returnHour"];
				int User_ID = (int) rs["User_ID"];
				User user = null;
				if ((criteria.Users != null) && (criteria.Users.Keys.Contains(User_ID))) {
					user = criteria.Users[User_ID];
				}

				if (user == null) {
					if (unknownUserIDs.Keys.Contains(User_ID)) {
						user = unknownUserIDs[User_ID];
					} else { //We don't already know about user and haven't marked yet it for query
						user = new User();
						user.Id = User_ID;
						unknownUserIDs[User_ID] = user;
					}
				}

				if (user != null) { //We know the user
					user.Schedules.Add(schedule);
					schedule.User = user;
				}

				result.Add(schedule);
			}
			rs.Close();

			//Now query unknow users
			if (unknownUserIDs.Count > 0) {
				IProfileDAO profileDAO = AbstractDAOFactory.getInstance(AbstractDAOFactory.Type.MySQL).createProfileDAO();
				UserSearchCriteria userCriteria = new UserSearchCriteria();
				userCriteria.Ids = unknownUserIDs;
				profileDAO.readAccounts(userCriteria);

				IDestinationDAO destinationDAO = AbstractDAOFactory.getInstance(AbstractDAOFactory.Type.MySQL).createDestinationDAO();
				DestinationSearchCriteria destinationCriteria = new DestinationSearchCriteria();
				destinationCriteria.Users = unknownUserIDs;
				destinationDAO.readDestinations(destinationCriteria);
			}
		}

		return result;
	}
}
